import os
from datetime import datetime
from turtle import pd

from testcases.amazon_auto_publish import evn
from testcases.amazon_auto_publish.api.common_api import api_detect_language, api_Infringement
from testcases.amazon_auto_publish.api.publish_es import api_query_es
from testcases.amazon_auto_publish.script.logger import ERROR, INFO, WARNING
from utils.mysqlUtils.con_mangodb import con_mangodb
from utils.mysqlUtils.con_sqlserver import connect_sqlserver
from utils.mysqlUtils.db_utils import DB

# 生产环境,刊登+调价的生产
from utils.mysqlUtils.mysql_control import MysqlDB
from utils.mysqlUtils.sql_server_utils import MSSQL

address1 = 'url1'
address2 = 'url2'
# mangodb1
address3 = 'url3'
# mangodb2
address4 = 'url4'
address5 = 'url5'






# 长度
class CaseLength:
    # 验证标题的长度
    def __init__(self, results, sku_id=None):
        if sku_id is None:
            sku_id = []
        self.title = []
        self.keywords = []
        self.description = []
        self.bullet_points1 = []
        self.bullet_points2 = []
        self.bullet_points3 = []
        self.bullet_points4 = []
        self.bullet_points5 = []
        self.site_code = []
        self.sku_id = []
        for i in range(len(results)):
            if len(results) == 0:
                print("此OrderSourceSKUID没有属性数据", sku_id[i])
            else:
                self.site_code.append(results[i]["SiteCode"])
                sku_id = results[i]["OrderSourceSkuId"]
                self.sku_id.append(sku_id)
                results_attributes = results[i]["Attrubute"]
                for j in results_attributes:
                    if j["AttributeFieldName"] == "item_name":
                        self.title.append(j["AttributeValue"])
                    elif j["AttributeFieldName"] == "bullet_point1":
                        self.bullet_points1.append(j["AttributeValue"])
                        # print(self.bullet_points1)
                    elif j["AttributeFieldName"] == "bullet_point2":
                        self.bullet_points2.append(j["AttributeValue"])
                    elif j["AttributeFieldName"] == "bullet_point3":
                        self.bullet_points3.append(j["AttributeValue"])
                    elif j["AttributeFieldName"] == "bullet_point4":
                        self.bullet_points4.append(j["AttributeValue"])
                    elif j["AttributeFieldName"] == "bullet_point5":
                        self.bullet_points5.append(j["AttributeValue"])
                    elif j["AttributeFieldName"] == "generic_keywords":
                        self.keywords.append(j["AttributeValue"])
                    elif j["AttributeFieldName"] == "product_description":
                        self.description.append(j["AttributeValue"])
                    else:
                        pass

    def length_title(self):
        for i in range(len(self.site_code)):
            if self.site_code[i] == "CA":
                if 50 <= len(self.title[i]) <= 100:
                    pass
                else:
                    INFO.logger.error(
                        f"OrderSourceSKUID为{self.sku_id[i]},标题长度为：{len(self.title[i])},不符合要求 {self.title[i]}")
                    # print("OrderSourceSKUID为%s,标题长度为：%s,不符合要求 %s" % (self.sku_id[i], len(self.title[i]), self.title[i]))
            if self.site_code[i] == "JP":
                if 50 <= len(self.title[i]) <= 90:
                    pass
                else:
                    INFO.logger.error(
                        f"OrderSourceSKUID为{self.sku_id[i]},标题长度为：{len(self.title[i])},不符合要求 {self.title[i]}")
                    # print("OrderSourceSKUID为%s,标题长度为：%s,不符合要求 %s" % (self.sku_id[i], len(self.title[i]), self.title[i]))

            else:
                if 50 <= len(self.title[i]) <= 180:
                    pass
                else:
                    INFO.logger.error(
                        f"OrderSourceSKUID为{self.sku_id[i]},标题长度为：{len(self.title[i])},不符合要求 {self.title[i]}")
                    # print("OrderSourceSKUID为%s,标题长度为：%s,不符合要求 %s" % (self.sku_id[i], len(self.title[i]), self.title[i]))

    def length_key_words(self):
        for i in range(len(self.keywords)):
            keywords = self.keywords[i].replace("\r\n", ",")
            if 200 <= len(keywords) <= 270:
                pass
            else:
                INFO.logger.error(
                    f"OrderSourceSKUID为{self.sku_id[i]}关键词长度的为：{len(keywords)}不符合要求{keywords}")

    def length_point(self):
        for i in range(len(self.bullet_points1)):
            if len(self.bullet_points1[i]) <= 500:
                pass
            else:
                INFO.logger.error(
                    f"OrderSourceSKUID为{self.sku_id[i]}亮点1长度的为：{len(self.bullet_points1[i])}不符合要求{self.bullet_points1[i]}")
        for i in range(len(self.bullet_points2)):
            if len(self.bullet_points2[i]) <= 500:
                pass
            else:
                INFO.logger.error(
                    f"OrderSourceSKUID为{self.sku_id[i]}亮点2长度的为：{len(self.bullet_points2[i])}不符合要求{self.bullet_points2[i]}")
        for i in range(len(self.bullet_points3)):
            if len(self.bullet_points3[i]) <= 500:
                pass
            else:
                INFO.logger.error(
                    f"OrderSourceSKUID为{self.sku_id[i]}亮点3长度的为：{len(self.bullet_points3[i])}不符合要求{self.bullet_points3[i]}")
        for i in range(len(self.bullet_points4)):
            if len(self.bullet_points4[i]) <= 500:
                pass
            else:
                INFO.logger.error(
                    f"OrderSourceSKUID为{self.sku_id[i]}亮点4长度的为：{len(self.bullet_points4[i])}不符合要求{self.bullet_points4[i]}")
        for i in range(len(self.bullet_points5)):
            if len(self.bullet_points5[i]) <= 500:
                pass
            else:
                INFO.logger.error(
                    f"OrderSourceSKUID为{self.sku_id[i]}亮点5长度的为：{len(self.bullet_points5[i])}不符合要求{self.bullet_points5[i]}")

    def length_descript(self):
        for i in range(len(self.description)):
            if len(self.description[i]) <= 1800:
                pass
            else:
                INFO.logger.error(
                    f"OrderSourceSKUID为{self.sku_id[i]}描述长度的为：{len(self.description[i])}不符合要求{self.description[i]}")

    def main_length(self):
        self.length_title()
        self.length_key_words()
        self.length_point()
        self.length_descript()


# 翻译
class CaseTranslate:
    def __init__(self, results, sku_id=None):
        if sku_id is None:
            sku_id = []
        self.title = []
        self.keywords = []
        self.description = []
        self.bullet_points = []
        self.site_code = []
        self.translate_data = []
        self.language = {'BR': 'pt', 'US': 'en', 'IT': 'it', 'DE': 'de', 'FR': 'fr', 'JP': 'ja', 'ES': 'es_control', 'PL': 'pl',
                         'NL': 'nl', 'SE': 'ko', 'MX': 'en',
                         'AE': 'en',
                         "GB": 'en', "CA": 'en', "AU": 'en', "IN": 'en', "SG": 'en', "SA": 'en', "BE": "fr"}
        for i in range(0,len(sku_id)):
            dict_translate_data = {}
            # results = con_mangodb("Amazon_AutoPublish", "AutoPublishUploadProductQueue", evn, address1,
            #                     OrderSourceSKUID=sku_id[i])
            attributes_results = results[i]["Attrubute"]
            # print(results[0]['Title'])
            if len(results) == 0:
                INFO.logger.error(
                    f"翻译验证：此OrderSourceSKUID没有数据{sku_id[i]}")
            else:
                dict_translate_data['SiteCode'] = str(results[i]['SiteCode'])
                dict_translate_data['OrderSourceSku'] = results[i]['OrderSourceSku']
                for j in attributes_results:
                    if j["AttributeFieldName"] in (
                            "item_name", "generic_keywords", "product_description", "bullet_point1", "bullet_point2",
                            "bullet_point3", "bullet_point4", "bullet_point5"):
                        dict_translate_data[j["AttributeFieldName"]]=j["AttributeValue"]
            self.translate_data.append(dict_translate_data)
        # print(self.translate_data)

    # 验证标题的翻译结果
    def translate(self):
        for Attribute in self.translate_data:
            for key in Attribute.keys():
                if key in (
                            "item_name", "generic_keywords", "product_description", "bullet_point1", "bullet_point2",
                            "bullet_point3", "bullet_point4", "bullet_point5"):
                    # 调用翻译判断接口
                    detect_data = api_detect_language(Attribute[key],self.language[Attribute['SiteCode']])
                    # 预期结果
                    expect_language = self.language[Attribute['SiteCode']]
                    # 获取翻译接口返回数据中的language
                    for j in detect_data:
                        if j[1] == expect_language:
                            pass
                        else:
                            INFO.logger.error(
                                f"接口检测结果为{j[1]}, 实际结果为：{expect_language} ")
                            INFO.logger.error(
                                f"OrderSourceSKU为{Attribute['OrderSourceSku']}对应的站点为：{Attribute['SiteCode']} 有数据未翻译成对应的语言{self.language[Attribute['SiteCode']]}")
                            # print("接口检测结果为：%s" % j["language"], "实际结果为：%s" % expect_language)
                            # print("OrderSourceSKUID为%s对应的站点为：%s有数据未翻译成对应的语言" % (i, self.translate_data[i][0]), j["keyword"])



# 去重
class CaseCompareRepetition:
    def __init__(self, sku_id, compareRepetition_results):

        self.title = []
        self.keywords = []
        self.description = []
        self.bullet_points = []
        # 在AmazonListingInfo把同站点所有数据查询出来
        for i in range(len(sku_id)):
            # results = con_mangodb("Amazon_AutoPublish", "AmazonListingInfo", evn, address1, OrderSourceSKUID=sku_id[i])
            # print(results[0]['Title'])
            if len(compareRepetition_results) == 0:
                INFO.logger.error(
                    f"去重验证:此OrderSourceSKUID没有数据{sku_id[i]}")
                # print("去重验证:此OrderSourceSKUID没有数据", sku_id[i])
            else:
                self.title.append(compareRepetition_results[i]['Title'])
                self.keywords.append(compareRepetition_results[i]['Generickeywords'])
                # print(results[0]['Description'])
                self.description.append(compareRepetition_results[i]['Description'])
                # print(results[0]['BulletPoints'][0])
                self.bullet_points.append(compareRepetition_results[i]['BulletPoints'])

    def compare_title(self, length=50):
        '''length:多少字符算重复'''
        if len(self.title) > 0:
            # for title
            y = len(self.title[0]) - length + 2
            z = length - 1
            e = len(self.title)
            for j in range(y):
                new_data = self.title[0][j:z]
                z = z + 1
                for k in range(1, e):
                    if new_data in self.title[k]:
                        INFO.logger.error(
                            f"重复title:{new_data} , 参照数据{k}")
                        # print("重复title:", new_data, "参照数据", k)
                    else:
                        pass
        else:
            INFO.logger.error(
                f"去重验证的标题数据还没有同步至erp")

    def compare_description(self, length=300):
        '''length:多少字符算重复'''
        if len(self.description) > 0:
            y = len(self.description[0]) - length + 2
            z = length - 1
            e = len(self.description)
            for j in range(y):
                new_data = self.description[0][j:z]
                z = z + 1
                for k in range(1, e):
                    if new_data in self.description[k]:
                        INFO.logger.error(
                            f"重复description:{new_data}, 参照数据{k}")
                    else:
                        pass
        else:
            INFO.logger.error(
                f"去重验证的描述数据还没有同步至erp")

    def compare_bullet_point(self):
        if len(self.bullet_points) > 0:
            for i in range(len(self.bullet_points) - 1):
                # 第一个OrderSourceSKUID的所有亮点
                new_data = self.bullet_points[0]
                for j in range(len(new_data)):
                    # 把每个亮点和其他两个集合的数据进行对比
                    if new_data[j] in self.bullet_points[i + 1]:
                        INFO.logger.error(
                            f"bullet_point重复:{new_data[j]},参照数据{i + 1}")
                    else:
                        pass
        else:
            INFO.logger.error(
                f"去重验证的亮点数据还没有同步至erp")

    def compare_keyword(self):
        if len(self.keywords) > 0:
            for i in range(len(self.keywords) - 1):
                new_data = self.keywords[0]
                if new_data == self.keywords[i + 1]:
                    # 判断两个字符串是否为空
                    if not new_data and not self.keywords[i + 1]:
                        pass
                    else:
                        INFO.logger.error(
                            f"keyword重复:{new_data},参照数据{i + 1}")
                else:
                    pass
        else:
            INFO.logger.error(
                f"去重验证的关键词数据还没有同步至erp")

    def main_compare_repetition(self):
        self.compare_title()
        self.compare_description()
        self.compare_bullet_point()
        self.compare_keyword()


# 敏感词
class CaseSensitiveWords():
    def __init__(self, results,Delivery_method,sku_id=None):
        self.skuid = []
        self.title = []
        self.keywords = []
        self.description = []
        self.bullet_points1 = []
        self.bullet_points2 = []
        self.bullet_points3 = []
        self.bullet_points4 = []
        self.bullet_points5 = []
        self.site_code = []
        self.title_version = []
        self.results = results
        # 查出所有数据，输出格式 self.title=[（skuid,sitecode,title）,（skuid,sitecode,title）]
        if len(results) > 0:
            for result in results:
                Attributes = result["Attrubute"]
                self.skuid.append(result["OrderSourceSkuId"])
                item_name=""
                bullet_point1=""
                bullet_point2 = ""
                bullet_point3 = ""
                bullet_point4 = ""
                bullet_point5 = ""
                generic_keywords = ""
                product_description= ""
                for Attribute in Attributes:
                    if Attribute["AttributeFieldName"] == "item_name":
                        item_name = Attribute["AttributeValue"]
                    if Attribute["AttributeFieldName"] == "bullet_point1":
                        bullet_point1 = Attribute["AttributeValue"]
                    if Attribute["AttributeFieldName"] == "bullet_point2":
                        bullet_point2 = Attribute["AttributeValue"]
                    if Attribute["AttributeFieldName"] == "bullet_point3":
                        bullet_point3 = Attribute["AttributeValue"]
                    if Attribute["AttributeFieldName"] == "bullet_point4":
                        bullet_point4 = Attribute["AttributeValue"]
                    if Attribute["AttributeFieldName"] == "bullet_point5":
                        bullet_point5 = Attribute["AttributeValue"]
                    if Attribute["AttributeFieldName"] == "generic_keywords":
                        generic_keywords = Attribute["AttributeValue"]
                    if Attribute["AttributeFieldName"] == "product_description":
                        product_description = Attribute["AttributeValue"]
                    else:
                        pass
                if item_name:
                    title_data = (result["OrderSourceSkuId"], result["SiteCode"], item_name)
                    self.title.append(title_data)
                if generic_keywords:
                    generic_keywords = (result["OrderSourceSkuId"], result["SiteCode"], generic_keywords, Delivery_method)
                    self.keywords.append(generic_keywords)
                if product_description:
                    product_description = (result["OrderSourceSkuId"], result["SiteCode"], product_description)
                    self.description.append(product_description)
                if bullet_point1:
                    bullet_point1 = (result["OrderSourceSkuId"], result["SiteCode"], bullet_point1)
                    self.bullet_points1.append(bullet_point1)
                if bullet_point2:
                    bullet_point2 = (result["OrderSourceSkuId"], result["SiteCode"], bullet_point2)
                    self.bullet_points2.append(bullet_point2)
                if bullet_point3:
                    bullet_point3 = (result["OrderSourceSkuId"], result["SiteCode"], bullet_point3)
                    self.bullet_points3.append(bullet_point3)
                if bullet_point4:
                    bullet_point4 = (result["OrderSourceSkuId"], result["SiteCode"], bullet_point4)
                    self.bullet_points4.append(bullet_point4)
                if bullet_point5:
                    bullet_point5 = (result["OrderSourceSkuId"], result["SiteCode"], bullet_point5)
                    self.bullet_points5.append(bullet_point5)
        else:
            print("此OrderSourceSKUID没有Attrubute数据", sku_id)
        # print("self.title",self.title)

    def get_senstive_words(self):

        senstive_words_results = con_mangodb("zhcxkj_amazon", "AutoPublishWordFilterMgoModel", 2, address3)
        country_data = {"PL": [],"US": [], "AE": [], "AU": [], "BR": [], "CA": [], "DE": [], "ES": [], "FR": [], "GB": [],
                        "IN": [],
                        "IT": [], "JP": [], "MX": [], "NL": [], "SA": [], "SE": [],
                        "SG": [], "TR": [],
                        "BE": []}

        for i in range(0, len(senstive_words_results)):
            Keyword = senstive_words_results[i]["Keyword"].lower()
            for j in (senstive_words_results[i]["SiteCodes"]):
                SiteCode = j
                country_data[SiteCode].append(Keyword)
        print("所有敏感词", country_data)
        return country_data

    def verify_senstive_title(self, senstive_sitecodes):
        '''
        senstive_sitecodes:站点对应的所有敏感词
        skuids：ordersouceskuid'''
        for i in range(0, len(self.results)):
            skuid = self.title[i][0]
            # 待验证的标题的国家
            sitecode = self.title[i][1]
            # 待验证的标题
            title = self.title[i][2].lower()
            senstive_sitecode = senstive_sitecodes[sitecode]
            for senstive in senstive_sitecode:
                if senstive in title:
                    print("标题有敏感词", skuid, senstive)
                else:
                    pass

    def verify_senstive_key_word(self, senstive_sitecodes):
        for i in range(0, len(self.results)):
            skuid = self.keywords[i][0]
            # 待验证的关键词的国家
            sitecode = self.keywords[i][1]
            # 待验证的关键词的标题版本
            titleversion = self.keywords[i][3]
            # 待验证的关键词
            key_words = self.keywords[i][2].lower()
            # key_words = key_words.split("\n\n\n")
            key_words = key_words.split("\r\n")
            senstive_sitecode = senstive_sitecodes[sitecode]
            # 增加默认词
            special_word = ["that", "be", "been", "being", "a", "an", "the", "and", "or", "as", "of", "at", "by",
                            "for", "with"]
            senstive_sitecode_key_words = senstive_sitecode + special_word
            for key_word in key_words:
                if titleversion == "FBA":
                    # 把关键词拆成词根，以列表形式输出
                    key_word = key_word.split()
                    find_senstive_data = [x for x in senstive_sitecode_key_words if x in key_word]
                    if len(find_senstive_data) > 0:
                        print("关键词有敏感词", skuid, find_senstive_data)
                else:
                    find_senstive_data2 = [x for x in key_words if x in senstive_sitecode_key_words]
                    if len(find_senstive_data2) > 0:
                        print("关键词有敏感词", skuid, find_senstive_data2)

    def verify_senstive_description(self, senstive_sitecodes):
        for i in range(0, len(self.results)):
            skuid = self.description[i][0]
            # 待验证的标题的国家
            sitecode = self.description[i][1]
            # 待验证的描述
            descript_data = self.description[i][2].lower()

            senstive_sitecode = senstive_sitecodes[sitecode]
            for senstive in senstive_sitecode:
                if senstive in descript_data:
                    print("descript_data有敏感词", skuid, senstive)
                else:
                    pass

    def verify_senstive_point(self, senstive_sitecodes):
        for i in range(0, len(self.results)):
            skuid = self.bullet_points1[i][0]
            # 待验证的标题的国家
            sitecode = self.bullet_points1[i][1]
            # 待验证的亮点1
            point1 = str(self.bullet_points1[i][2]).lower()
            # 待验证的亮点2
            point2 = str(self.bullet_points2[i][2]).lower()
            # 待验证的亮点3
            point3 = str(self.bullet_points3[i][2]).lower()
            # 待验证的亮点4
            point4 = str(self.bullet_points4[i][2]).lower()
            # 待验证的亮点5
            point5 = str(self.bullet_points5[i][2]).lower()
            senstive_sitecode = senstive_sitecodes[sitecode]
            for senstive in senstive_sitecode:
                if senstive in point1:
                    print("point1有敏感词", skuid, senstive)
                elif senstive in point2:
                    print("point2有敏感词", skuid, senstive)
                elif senstive in point3:
                    print("point3有敏感词", skuid, senstive)
                elif senstive in point4:
                    print("point4有敏感词", skuid, senstive)
                elif senstive in point5:
                    print("point5有敏感词", skuid, senstive)
                else:
                    pass

    def main_verify_senstive(self):
        skuids = self.skuid
        # 站点对应的敏感词
        senstive_sitecodes = self.get_senstive_words()
        self.verify_senstive_title(senstive_sitecodes)
        self.verify_senstive_key_word(senstive_sitecodes)
        self.verify_senstive_description(senstive_sitecodes)
        self.verify_senstive_point(senstive_sitecodes)


# 侵权验证
class CaseInfringement():
    def __init__(self, results, sku_id=None):
        if sku_id is None:
            sku_id = []
        self.Infringement_datas = []
        self.skuid = sku_id
        self.queueids = []
        self.sku=[]
        # 查出所有数据，输出格式 [{skuid,sitecode,CategoryPathId,keyWords,queueid},{skuid,sitecode,CategoryPathId,keyWords}]
        if len(results) > 0:
            for result in results:
                Attributes = result["Attrubute"]
                Sku = result["Sku"]
                # 要验证的通用队列的数据
                appoint_data = ["item_name", "bullet_point1", "bullet_point_2", "bullet_point_3", "bullet_point_4",
                                "bullet_point_5",
                                "generic_keywords", "product_description"]
                keyWords = {}
                for Attribute in Attributes:
                    if Attribute["AttributeFieldName"] in appoint_data:
                        keyWord = {Attribute["AttributeFieldName"]: Attribute["AttributeValue"]}
                        keyWords = dict(keyWords, **keyWord)
                    else:
                        pass
                CategoryPathId = None
                Infringement_data = {"OrderSourceSKUID": result["OrderSourceSkuId"], "SiteCode": result["SiteCode"],
                                     "CategoryPathId": CategoryPathId, "keyWords": keyWords, "Sku": Sku}
                self.Infringement_datas.append(Infringement_data)
                self.sku.append(Sku)
        else:
            print("此OrderSourceSKUID没有Attributes数据", sku_id)

        # print("Infringement_datas", self.Infringement_datas)

    def get_browse_path_by_id_data(self,sku):
        #sku=('14223844','7455030','4695402','4082235','14724317','15448757','14961474','15406943','8080580','14921045','10643255')
        skus=tuple(x for x in sku)
        sql = f"select sku,BrowsePathByID from E_Product_Amazon_BrowseTree where sku in {skus} and IsMain = 1 and IsDelete = 0"
        '''browse_path_by_id_data = MSSQL(yaml_path=1, database_name=1).__GetConnect_dict()'''
        print("查询erp的类目路径sql:",sql)
        browse_path_by_id_data = connect_sqlserver(4, sql)
        # print("erp的类目路径",browse_path_by_id_data)
        return browse_path_by_id_data


    def verify_Infringement(self):
        # 查询所有的类目id
        #skus=('16126291', '16177948', '14486333', '13622174', '2789312', '17671824', '17151668', '3999851', '15498970', '13622174', '2789312', '3999851')
        erp_datas = self.get_browse_path_by_id_data(tuple(self.sku))
        # 查把类目id赋值至对应的skuid中,
        for erp_data in erp_datas:
            get_browse_path_by_id_data = erp_data["BrowsePathByID"]
            sku = erp_data["sku"]
            for Infringement_data in self.Infringement_datas:
                if Infringement_data["Sku"] == sku:
                    Infringement_data["CategoryPathId"] =get_browse_path_by_id_data
                else:
                    pass
        #print(self.Infringement_datas)
        # 然后调用接口验证是否有侵权词
        api_in_SensitiveId = []
        for Infringement_data in self.Infringement_datas:
            siteCode = Infringement_data["SiteCode"]
            CategoryPathId = Infringement_data["CategoryPathId"]
            keyWords = list(Infringement_data["keyWords"].values())
            api_datas = api_Infringement(siteCode, CategoryPathId, keyWords)

            for api_data in api_datas:
                    #把敏感词校验合并接口返回的敏感词id全部提取出来
                    if len(api_data["SensitiveWords"]) > 0:
                        SensitiveWords_lenth=len(api_data["SensitiveWords"])
                        for i in range(0, SensitiveWords_lenth):
                            api_in_SensitiveId.append(str(api_data["SensitiveWords"][i]["SensitiveId"]))
                    if len(api_data["ProductBrands"]) > 0:
                        BrandWords_lenth = len(api_data["BrandWords"])
                        for i in range(0,BrandWords_lenth):
                            api_in_SensitiveId.append(str(api_data["BrandWords"][i]["SensitiveId"]))
                    if len(api_data["SensitiveWords"]) > 0 or len(api_data["BrandWords"]) > 0 or len(
                            api_data["ProductBrands"]) > 0:
                        print("调用侵权词接口验证结果", Infringement_data["Sku"], api_data)
                    else:
                        pass

        api_in_SensitiveId=list(set(api_in_SensitiveId))
        print("敏感词接口返回的敏感词id", api_in_SensitiveId)
        #校验敏感词校验合并接口返回的敏感词是否有已审核通过的词
        Sensitive_Audit_results = con_mangodb("zhcxkj_riskworker", "Sensitive_Audit", 2, address5,
                              SensitiveId={"$in": api_in_SensitiveId}, AuditStatus=1)
        if len(Sensitive_Audit_results)>0:
            print("通用队列上传的资料存在审核通过的敏感词",Sensitive_Audit_results)
        print("侵权词验证完毕")


# 继承父类，重写__init__
class GetPublishData(CaseLength):
    def __init__(self):
        data = pd.read_excel('fba的测试数据.xlsx')
        # 以列表形式输出指定列数据
        self.title = data["标题"].tolist()
        self.keywords = data["关键词"].tolist()
        self.description = data["描述"].tolist()
        self.bullet_points1 = data["亮点1"].tolist()
        self.bullet_points2 = data["亮点2"].tolist()
        self.bullet_points3 = data["亮点3"].tolist()
        self.bullet_points4 = data["亮点4"].tolist()
        self.bullet_points5 = data["亮点5"].tolist()
        self.site_code = data["sitecode"].tolist()
        self.sku_id = data["sku"].tolist()


def get_cold_skuid(sku, OrderSourceCountry):
    filePath = os.path.abspath("../../../config/config.yaml")
    cold_base = DB(filePath, "mysql_db_code_base")
    sql1 = "SELECT OrderSourceSKUID FROM aml_amazon_product_base_dep81 WHERE sku = '%s' and OrderSourceCountry='%s'" % (
        sku, str(OrderSourceCountry))
    sql2 = "SELECT OrderSourceSKUID FROM aml_amazon_product_base_dep84 WHERE sku = '%s' and OrderSourceCountry='%s'" % (
        sku, OrderSourceCountry)
    sql3 = "SELECT OrderSourceSKUID FROM aml_amazon_product_base_dep85 WHERE sku = '%s' and OrderSourceCountry= '%s'" % (
        sku, OrderSourceCountry)
    sql4 = "SELECT OrderSourceSKUID FROM aml_amazon_product_base_dep86 WHERE sku = '%s' and OrderSourceCountry= '%s'" % (
        sku, OrderSourceCountry)
    sql5 = "SELECT OrderSourceSKUID FROM aml_amazon_product_base_dep87 WHERE sku = '%s' and OrderSourceCountry= '%s'" % (
        sku, OrderSourceCountry)
    sql6 = "SELECT OrderSourceSKUID FROM aml_amazon_product_base_dep88 WHERE sku = '%s' and OrderSourceCountry= '%s'" % (
        sku, OrderSourceCountry)
    sql7 = "SELECT OrderSourceSKUID FROM aml_amazon_product_base_dep89 WHERE sku = '%s' and OrderSourceCountry= '%s'" % (
        sku, OrderSourceCountry)
    sql8 = "SELECT OrderSourceSKUID FROM aml_amazon_product_base_dep123 WHERE sku = '%s' and OrderSourceCountry= '%s'" % (
        sku, OrderSourceCountry)
    sql9 = "SELECT OrderSourceSKUID FROM aml_amazon_product_base_dep410 WHERE sku = '%s' and OrderSourceCountry= '%s'" % (
        sku, OrderSourceCountry)
    sql10 = "SELECT OrderSourceSKUID FROM aml_amazon_product_base_dep500 WHERE sku = '%s' and OrderSourceCountry= '%s'" % (
        sku, OrderSourceCountry)
    sql11 = "SELECT OrderSourceSKUID FROM aml_amazon_product_base_dep9999 WHERE sku = '%s' and OrderSourceCountry= '%s'" % (
        sku, OrderSourceCountry)
    cold_skuid_data = []
    for i in [sql1, sql2, sql3, sql4, sql5, sql6, sql7, sql8, sql9, sql10, sql11]:
        cold_res = cold_base.execute_sql_many(i)
        cold_skuid_data.append(cold_res)

    skuid = []
    for i in cold_skuid_data:
        if type(i) is list:
            for j in i:
                skuid.append(j["OrderSourceSKUID"])
    return skuid


def main_publish():
    es_data = api_query_es()
    skuids = []
    for i in es_data:
        # 取出所有OrderSourceSKUID的数据
        # skuid="NumberLong"+(i["OrderSourceSKUID"])
        skuids.append(int(i["OrderSourceSKUID"]))

        # cold_skuid = get_cold_skuid(i["OrderSourceSKUID"], i["SiteCode"])
        # repeat_skid = [i["OrderSourceSKUID"]] + cold_skuid
        repeat_skid = [i["OrderSourceSKUID"]]
        # 标题去重验证# 描述去重验证# 亮点去重验证# 关键词去重验证
        compareRepetition_results = con_mangodb("Amazon_AutoPublish", "AutoPublishUploadProductQueue", 2, address1,
                                                OrderSourceSKUID={"$in": repeat_skid})
        # CaseCompareRepetition(repeat_skid, compareRepetition_results).main_compare_repetition()
    # 指定数据验证
#        ,"11145192276522","171553192273647","145845192274608","145643192277185","201132192274990","164614192274867"

    skuids = ["OY268564602K9J26"]

    print("验证的litingid", skuids)
    # 上架队列
    '''results = con_mangodb("Amazon_AutoPublish", "AutoPublishUploadProductQueue", 2, address1,
                          OrderSourceSKUID={"$in": skuids})'''
    # 通用队列
    AddTime_value = datetime.fromisoformat('2024-07-03T16:12:45')
    results = con_mangodb("Amazon_BuryWord", "Amazon_Common_Queue", 2, address1,
                          OrderSourceSku={"$in": skuids},AddTime={"$gt":AddTime_value
        },Type=6)
    #验证侵权词
    #CaseInfringement(results, skuids).verify_Infringement()
    # 验证标题长度# 关键词长度# 亮点长度# 描述长度
    #CaseLength(results, skuids).main_length()
    # 敏感词标题验证# 敏感词关键词验证 # 敏感词描述验证# 敏感词亮点验证
    #CaseSensitiveWords(results,"FBM",skuids).main_verify_senstive()
    # 验证是否翻译，未翻译成对应语言会输出
    CaseTranslate(results, skuids).translate()


if __name__ == '__main__':
    main_publish()
